Query settings (available from Query Options on the Query ribbon) govern the operation of each query in a specific Discover report. They allow users to tweak the way a query is resolved and to improve performance. Once set, they drive the report and its subsequent usage in the platform.
The types of query settings available are:
Cache Options
The cache options govern whether a query result should be retrieved from the cache on Pyramid's servers (if available), or a new query should be run against the underlying data source every time. The main purpose of caching is to accelerate the re-use of the same query result set without re-querying the underlying data source for the same results.
From the Query ribbon in Discover Pro, click Query Settings > Cache Options > <Caching Option>.
There are 3 caching options:
- Model Default: Use the same caching options as the underlying data model. These options are set in the admin console or in the model definition.
- Disable Cache: Always re-query the underlying data source, ignoring any cached results from previous queries.
- Enable Cache: Avoid re-querying the underlying data source, use cached results if they exist.
Caching Mechanics
The caching mechanism does NOT store the query result permanently. Instead, it is held in memory in case the same query is required in the future. Since there are numerous complications with such a mechanism, there are many aspects of the platform that govern if and how queries are cached.
- The amount of time a query is held in the cache, if caching is enabled, is determined in the admin console:
- If the query cache setting is "0", the cache is effectively turned off globally.
- The cache is held in memory. If the server's memory starts reaching limits, queries may be released from the cache earlier than the specified time.
- If the data source is secured by user, the cache stores query results by user. This means that the same query executed by different users do not overlap in the cache.
- For data models designed in Pyramid, it's possible to determine the default caching mode to use for all queries run against it. By default, caching is enabled in all Pyramid models and by default, the cache settings in Discover reflect the settings made in the data model.
- For MS OLAP, Tabular, and SAP BW data sources, caching generally doesn't work when the model is security enabled. This is because there is no way to share results across a userbase. You can, however, enable caching when using the Model Default option, if the data model management properties in the data source manager are set as follows:
- The Data model has NO data-level security checkbox is selected.
- The Data model uses Real-time direct queries or ROLAP checkbox is clear (deselected).
- For a SAP Hana source:
- Where Hana has a Semantic layer (Calculation View / Analytic View), the caching details are as described for MS OLAP, Tabular, and SAP BW above.
- Where Hana is a Pyramid Model, the caching details are like any other SQL data source.
Query Cache in Present and Publish
Any queries included in Present and Publish reflect the settings made in Discover Pro for the content. There is an option in Present to ignore cache settings.
Row Limit
The row limit defines the maximum number of rows that will be returned in the query results. For more information, see Row Limit.
Note: There may also be a MASTER query limit set by the administrator in the Admin Console. Where this is the case, Pyramid uses the lower of the two limits. For more information, see Query limits.
Optimization Settings
These settings govern the operation of the query in a specific Discover report.
Note: Optimization settings are only relevant for Microsoft Multidimensional and Tabular and SAP BW models and cubes. They are NOT relevant in Pyramid models.
The preceding example shows the Optimization Settings for Microsoft Multidimensional and Tabular. Other settings are available where the data source is SAP BW or SQL.
Optimization settings for Microsoft Multidimensional and Tabular
Microsoft's MDX engine has undergone numerous changes since its inception. Due to differences in versions, differences between Multidimensional and Tabular, as well as differences n cube / model design, there are some elements of the MDX query construction that need to be modulated. The following settings will allow advanced users to tweak how the MDX query is executed.
- Optimize: Enables 'non-empty' logic on all filters, N-of-Ns, and sort operations to improve processing times. This feature will not work in some cube scenarios or if the top level of a cube is empty.
- Measure Optimization: Enables 'non-empty' logic on measure selections to improve processing times. This feature will not work in some cube scenarios.
- Context Heuristics: Applies 'EXISTING' functional logic to various operations to force the injection of context values into different parts of the query when using two or more attributes from the same dimension. This feature is used to correct for logical errors in Microsoft's MDX engine.
- Auto-include Calculated Members: Enables all functional queries to include server-defined custom members in results.
- Sub Query Mode: Instructs the query engine to use sub-queries in MDX when making multiple background ("where") selections. Sub-querying can often be more efficient than normal query structures, however, they have limitations and cannot be used universally.
- Use Explicit Lists: Creates a list in the query's 'WITH' statement for any list or multi-select filter used in the Discovery report.
- Remove Filter Scoping: Removes the Scope_Isolation function from the filter query.
- Cross Filtering: If a regular filter is set in one report and a context-based multi-select filter based on a dynamic list is also selected, the elements in the second filter will be based on the selection in the first filter.
Optimization settings for SAP BW
- Sub Query Mode: As above.
Optimization settings for SQL (Relational Databases)
- Optimize: Enables the SQL engine to join multiple queries together to make a query run more efficiently. If this option is not selected, the Pyrana engine runs smaller but more fragmented queries. This feature is enabled by default for all new discoveries.
- Sub Query Mode: Instructs the query engine to use sub-queries. This enables the Pyrana engine to make use of the ANSI SQL sub-query functionality. This can be used to optimize a query execution to run multiple logical steps in one SQL query.
User Defaults
Set the default key settings from the user defaults capability in the admin console, the user's personal default settings, or both.